<?php
/*======================================================================
 避難所リストUpdateバッチ
 GoogleFusionTables上の避難所リストをPostgreSQL上のmst_shelterテーブルに
 論理キーでUpsertする。
======================================================================*/

//避難所リストURL
define('SHELTER_LIST_URL', "https://www.google.com/fusiontables/api/query?sql=SELECT%20*%20FROM%20590013");

//DB接続パラメータ
$db_host	= "localhost";
$db_port	= 5432;
$db_name	= "pairing_db";
$db_user	= "dummy_user";
$db_passwd	= "dummy_password";

//GoogleFusionTable列番号
define('COLNUM_PREFECTURE', 0);
define('COLNUM_AREA', 		1);
define('COLNUM_CITY', 		2);
define('COLNUM_DISTRICT', 	3);
define('COLNUM_NAME', 		4);
define('COLNUM_POPULATION', 5);
define('COLNUM_CAPACITY', 	6);
define('COLNUM_UPDATEDATE',	7);
define('COLNUM_UPDATETIME',	8);
define('COLNUM_SOURCE', 	9);
define('COLNUM_NOTES', 		10);
define('COLNUM_LATLNG', 	11);
define('COLNUM_COLOR', 		12);

//DBフィールド名定義
define('FLD_SHELTER_ID',	"shelter_id");
define('FLD_PREFECTURE',	"prefecture");
define('FLD_AREA',			"area");
define('FLD_CITY',			"city");
define('FLD_DISTRICT',		"district");
define('FLD_NAME',			"name");
define('FLD_POPULATION',	"population");
define('FLD_CAPACITY',		"capacity");
define('FLD_SOURCE',			"source");
define('FLD_SOURCE_UPDATE_DATE',"source_update_date");
define('FLD_SOURCE_UPDATE_TIME',"source_update_time");
define('FLD_LAT_LNG',		"lat_lng");
define('FLD_NOTES',			"notes");
define('FLD_STATUS',		"status");
//define('FLD_ENABLED',		"enabled");
define('FLD_CREATE_DATETIME',"create_datetime");
define('FLD_UPDATE_DATETIME',"update_datetime");

//更新ステータス
define('STATUS_PRE_COMITT_NEW',		0);	//確認前（新規）
define('STATUS_PRE_COMITT_UPDATE',	1);	//確認前（更新）
define('STATUS_COMITTED',			2);	//確認後
define('STATUS_DISABLED',			9);	//無効

//作業用ファイル等
define('WORK_DIR',		"/tmp/.okute");
define('TMP_GFT_CSV',	WORK_DIR."/shelter_list.csv");


//各種初期化
setlocale( LC_ALL, 'ja_JP.UTF-8' );
openlog($argv[0], LOG_PID, LOG_LOCAL6);
syslog(LOG_INFO, "start import.");
if (is_dir(WORK_DIR)) {
	deleteDir(WORK_DIR);
}
mkdir(WORK_DIR, 744);

//GFTから避難所リストをGET
$response = http_get_exec(SHELTER_LIST_URL);

//CSVデータを一度ファイルに落とす
$fp = fopen(TMP_GFT_CSV, "w");
flock($fp, LOCK_EX);
fputs($fp, $response['content']);
flock($fp, LOCK_UN);
fclose($fp);

syslog(LOG_INFO, "get shelter-list from [".SHELTER_LIST_URL."], HTTP-Response: [".$response['response']."], size=[".filesize(TMP_GFT_CSV)." bytes].");

if ($response['response']=='200') {
	//--------------------------------------------------
	// DBから現在のマスタをバッファリング
	//--------------------------------------------------
	$param="host=$db_host port=$db_port dbname=$db_name user=$db_user password=$db_passwd";
	$con = pg_connect($param);
	if (!$con) {
    	syslog(LOG_ERR, pg_last_error());
    	die();
	}
	syslog(LOG_DEBUG, "pgsql con=$con");

	$rs = pg_query($con, "select * from mst_shelter");
	$maxrows = pg_num_rows($rs);
	for ($i = 0; $i < $maxrows; $i++) {
		//先頭5列（prefecture -> name）の'/'結合文字列を論理キーにしてバッファリング
		$row = pg_fetch_array($rs, $i);
		$lkey = $row[FLD_PREFECTURE]
				."/".$row[FLD_AREA]
				."/".$row[FLD_CITY]
				."/".$row[FLD_DISTRICT]
				."/".$row[FLD_NAME];
		$buffer[$lkey] = $row;
	}

	$line = 0;
	$upd = 0;
	$ins = 0;
	$keep = 0;

	//CSV読み込み
	if (($handle = fopen(TMP_GFT_CSV, "r")) !== FALSE) {
		while (($data = fgetcsv($handle, 1024, ",", '"')) !== FALSE) {
			$line++;

			//ヘッダ行は読み飛ばす
			if ($line == 1) continue;

			//TODO ヘッダ行の値を見てスキーマ改変チェック

			$lkey = $data[COLNUM_PREFECTURE]
					."/".$data[COLNUM_AREA]
					."/".$data[COLNUM_CITY]
					."/".$data[COLNUM_DISTRICT]
					."/".$data[COLNUM_NAME];
			 if(isset($buffer[$lkey])){
			 	if (isUpdate($data, $buffer[$lkey])) {
			 		$fields = getUpdateParamArray($data);
			 		pg_update($con, 'mst_shelter', $fields, array(FLD_SHELTER_ID => $buffer[$lkey][FLD_SHELTER_ID]));
			 		$upd++;
			 	} else {
			 		$keep++;
			 	}
			 } else {
			 	$fields = getInsertParamArray($data);
			 	//print_r($fields);
			 	pg_insert($con, 'mst_shelter', $fields);
				$ins++;
			 }

			 //if ($line==2) break;
		}
		$line--;	//Header行分減算

		fclose($handle);
	}
	pg_close($con);
}

syslog(LOG_INFO, "finish import.[get=$line rec, new=$ins rec, update=$upd rec, keep=$keep]");

closelog();

/*---------------------------------------------
 Insert用フィールド配列を生成
 param $csv_rec GFTから取得したCSV1レコード分の配列
 return pg_insert用配列
---------------------------------------------*/
function getInsertParamArray($csv_rec) {
	$fields = array(
		FLD_PREFECTURE	=> quote_str($csv_rec[COLNUM_PREFECTURE], false),
		FLD_AREA		=> quote_str($csv_rec[COLNUM_AREA], false),
		FLD_CITY		=> quote_str($csv_rec[COLNUM_CITY], false),
		FLD_DISTRICT	=> quote_str($csv_rec[COLNUM_DISTRICT], false),
		FLD_NAME		=> quote_str($csv_rec[COLNUM_NAME], false),
		FLD_POPULATION	=> quote_str($csv_rec[COLNUM_POPULATION], true),
		FLD_CAPACITY	=> quote_str($csv_rec[COLNUM_CAPACITY], true),
		FLD_SOURCE		=> quote_str($csv_rec[COLNUM_SOURCE], true),
		FLD_SOURCE_UPDATE_DATE => quote_str($csv_rec[COLNUM_UPDATEDATE], true),
		FLD_SOURCE_UPDATE_TIME => quote_str($csv_rec[COLNUM_UPDATETIME], true),
		FLD_LAT_LNG		=> quote_str($csv_rec[COLNUM_LATLNG], true),
		FLD_NOTES		=> quote_str($csv_rec[COLNUM_NOTES], true),
		FLD_STATUS		=> STATUS_PRE_COMITT_NEW,
		FLD_CREATE_DATETIME => 'now()',
		FLD_UPDATE_DATETIME => 'now()'
		);
	return $fields;
}

/*---------------------------------------------
 Update用フィールド配列を生成
 param $csv_rec GFTから取得したCSV1レコード分の配列
 return pg_update用配列
---------------------------------------------*/
function getUpdateParamArray($csv_rec) {
	$fields = array(
		FLD_PREFECTURE	=> quote_str($csv_rec[COLNUM_PREFECTURE], false),
		FLD_AREA		=> quote_str($csv_rec[COLNUM_AREA], false),
		FLD_CITY		=> quote_str($csv_rec[COLNUM_CITY], false),
		FLD_DISTRICT	=> quote_str($csv_rec[COLNUM_DISTRICT], false),
		FLD_NAME		=> quote_str($csv_rec[COLNUM_NAME], false),
		FLD_POPULATION	=> quote_str($csv_rec[COLNUM_POPULATION], true),
		FLD_CAPACITY	=> quote_str($csv_rec[COLNUM_CAPACITY], true),
		FLD_SOURCE		=> quote_str($csv_rec[COLNUM_SOURCE], true),
		FLD_SOURCE_UPDATE_DATE => quote_str($csv_rec[COLNUM_UPDATEDATE], true),
		FLD_SOURCE_UPDATE_TIME => quote_str($csv_rec[COLNUM_UPDATETIME], true),
		FLD_LAT_LNG		=> quote_str($csv_rec[COLNUM_LATLNG], true),
		FLD_NOTES		=> quote_str($csv_rec[COLNUM_NOTES], true),
		FLD_STATUS		=> STATUS_PRE_COMITT_UPDATE,
		FLD_UPDATE_DATETIME => 'now()'
		);
	return $fields;
}


/*---------------------------------------------
 全フィールド更新チェック
 param $csv_rec GFTから取得したCSV1レコード分の配列
 param $db_rec DBから取得した1レコード分の配列
 return csv保管対象全フィールドのうち
 		ひとつでもDB側と一致しなければtrue、完全一致でfalse
---------------------------------------------*/
function isUpdate($csv_rec, $db_rec) {
	/*
	return (strcmp($csv_rec[COLNUM_PREFECTURE], $db_rec[FLD_PREFECTURE]) != 0
		|| strcmp($csv_rec[COLNUM_AREA], $db_rec[FLD_AREA]) != 0
		|| strcmp($csv_rec[COLNUM_CITY], $db_rec[FLD_CITY]) != 0
		|| strcmp($csv_rec[COLNUM_DISTRICT], $db_rec[FLD_DISTRICT]) != 0
		|| strcmp($csv_rec[COLNUM_NAME], $db_rec[FLD_NAME]) != 0
		|| strcmp($csv_rec[COLNUM_POPULATION], $db_rec[FLD_POPULATION]) != 0
		|| strcmp($csv_rec[COLNUM_CAPACITY], $db_rec[FLD_CAPACITY]) != 0
		|| strcmp($csv_rec[COLNUM_SOURCE], $db_rec[FLD_SOURCE]) != 0
		|| strcmp($csv_rec[COLNUM_UPDATEDATE], $db_rec[FLD_SOURCE_UPDATE_DATE]) != 0
		|| strcmp($csv_rec[COLNUM_UPDATETIME], $db_rec[FLD_SOURCE_UPDATE_TIME]) != 0
		|| strcmp($csv_rec[COLNUM_LATLNG], $db_rec[FLD_LAT_LNG]) != 0
		|| strcmp($csv_rec[COLNUM_NOTES], $db_rec[FLD_NOTES]) != 0);
	*/

	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_PREFECTURE, FLD_PREFECTURE)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_AREA, FLD_AREA)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_CITY, FLD_CITY)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_DISTRICT, FLD_DISTRICT)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_NAME, FLD_NAME)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_POPULATION, FLD_POPULATION)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_CAPACITY, FLD_CAPACITY)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_SOURCE, FLD_SOURCE)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_UPDATEDATE, FLD_SOURCE_UPDATE_DATE)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_UPDATETIME, FLD_SOURCE_UPDATE_TIME)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_LATLNG, FLD_LAT_LNG)) return true;
	if (checkUpdateFld($csv_rec, $db_rec, COLNUM_NOTES, FLD_NOTES)) return true;

	//syslog(LOG_INFO, "update not found. shelter_id=".$db_rec[FLD_SHELTER_ID]);
	return false;
}

/*---------------------------------------------
 単項目同一性チェック
---------------------------------------------*/
function checkUpdateFld($csv_rec, $db_rec, $csv_index, $fld_name) {
	$val_csv	= $csv_rec[$csv_index];
	$val_db		= $db_rec[$fld_name];

	//空文字とNULLは一致とみなす
	if (strlen($val_csv) == 0) $val_csv = 'NULL';
	if (strlen($val_db) == 0)  $val_db = 'NULL';

	if (strcmp($val_csv, $val_db) != 0) {
		syslog(LOG_INFO, "update found! shelter_id=".$db_rec[FLD_SHELTER_ID]." ".$fld_name.": ".$val_csv."!=".$val_db);
		return true;
	}
	return false;
}


/*---------------------------------------------
 SQL文字列パラメータをシングルクォートで囲む
 param パラメータ文字列
 param boolean true=空の場合に"NULL"を返す
 return クォート済みパラメータ
---------------------------------------------*/
function quote_str($val, $return_null) {

	if (strlen($val) == 0) {
		if ($return_null) {
			return "NULL";
		}
	}

	//return "'".$val."'";
	return $val;
}
/*---------------------------------------------
 HTTP GETリクエスト実行
 param URL
 return array('response'=HTTPレスポンスコード, 'content'=レスポンスBody)
---------------------------------------------*/
function http_get_exec($url) {

	$h=fopen($url,'r');
	$meta_data=stream_get_meta_data($h); 		// both PHP4, PHP5 OK
	$headers=$meta_data['wrapper_data'];
	$result['content']=stream_get_contents($h);	// only PHP5, you need write the function for PHP4
	fclose($h);

	if (preg_match('/^HTTP\/([0-9]\.[0-9]) ([0-9]+)/',$headers[0],$matches)) {
		$result['response']=$matches[2];
	}

 	return $result;
}


/* ディレクトリ削除メソッド */
function deleteDir($rootPath){
    $strDir = opendir($rootPath);
    while($strFile = readdir($strDir)){
        if($strFile != '.' && $strFile != '..'){  //ディレクトリでない場合のみ
            unlink($rootPath.'/'.$strFile);
        }
    }
    rmdir($rootPath);
}